from collections import Iterable
import sqlite3

from molly.bo import File, Storage
from settings import SQL_INIT_CATALOG


class BaseDAO(object):
    def __str__(self):
        return '%s %s' % (self.__class__, self.__dict__)

class CatalogDAO(BaseDAO):
    """Represents a connection to the sqlite3 database."""

    @classmethod
    def create_catalog(cls, catalog_file):
        catalog = cls(catalog_file)
        catalog.connect()
        catalog.init_db()
        return catalog

    def __init__(self, catalog_file, connect=False):
        self.catalog_file = catalog_file
        self.conn = None
        self.is_connected = False

        if connect:
            self.connect()

    def connect(self):
        self.conn = sqlite3.connect(self.catalog_file)
        self.conn.row_factory = sqlite3.Row
        self.conn.execute("PRAGMA foreign_keys = 1")
        self.is_connected = True

    def close(self):
        self.conn.close()
        self.is_connected = False

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def init_db(self):
        if not self.is_connected:
            self.connect()

        with open(SQL_INIT_CATALOG) as fh:
            self.conn.executescript(fh.read())

class MapperDAO(BaseDAO):
    # these attributes must be overriden
    _columns = () # database columns
    _sql_select = None
    _sql_by_id = None
    _sql_insert = None
    _sql_update = None

    def __init__(self, catalog):
        self.catalog = catalog
        self.conn = catalog.conn # handy alias

    def row_to_obj(self, row):
        raise NotImplementedError()

    def obj_to_row(self, obj):
        return dict([(f, getattr(obj, f)) for f in self._columns])

    def by_id(self, id_):
        assert self.catalog.is_connected, 'Catalog is not connected.'
        return self.row_to_obj(self.conn.execute(self._sql_by_id, [id_]).fetchone())

    def all(self):
        assert self.catalog.is_connected, 'Catalog is not connected.'
        return list(self.iterall())

    def iterall(self):
        assert self.catalog.is_connected, 'Catalog is not connected.'
        for row in self.conn.execute(self._sql_select):
            yield self.row_to_obj(row)

    def save(self, objects):
        assert self.catalog.is_connected, 'Catalog is not connected.'
        if isinstance(objects, Iterable):
            for obj in objects:
                self._save(obj)
        else:
            self._save(objects)
            
    def _save(self, obj):
        if obj.id is None:
            # insert
            self.conn.execute(self._sql_insert, self.obj_to_row(obj))
        else:
            # update
            self.conn.execute(self._sql_update, self.obj_to_row(obj))

class StorageDAO(MapperDAO):
    _columns = ('id', 'name')
    _sql_select = "SELECT %s FROM storage" % ', '.join(_columns)
    _sql_by_id = "SELECT %s FROM storage WHERE id = ?" % ', '.join(_columns)
    _sql_insert = None # init by module
    _sql_update = None # init by module

    def row_to_obj(self, row):
        return Storage(**row)

class FileDAO(MapperDAO):
    _columns = ('id', 'storage_id', 'name', 'file_type', 'parent')
    _sql_select = "SELECT %s FROM file" % ', '.join(_columns)
    _sql_by_id = "SELECT %s FROM file WHERE id = ?" % ', '.join(_columns)
    _sql_insert = None # init by module
    _sql_update = None # init by module

    def row_to_obj(self, row):
        d = {}
        for k, v in [(k, row[k]) for k in row.keys()]:
            if k == 'storage_id':
                d['storage'] = Storage(id=v)
            else:
                d[k] = v

        return File(**d)

    def obj_to_row(self, obj):
        row = {}
        for f in self._columns:
            if f == 'storage_id':
                row[f] = obj.storage.id
            else:
                row[f] = getattr(obj, f)

        return row


def _init():
    """Initialize the DAO module."""

    # StorageDAO
    columns = list(StorageDAO._columns)
    columns.remove('id')
    StorageDAO._sql_insert = "INSERT INTO storage (%s) values (%s)" % (', '.join(columns), ', '.join([':%s' % f for f in columns]))
    StorageDAO._sql_update = "UPDATE storage SET %s WHERE id = :id" % ', '.join(['%s = :%s' % (f, f) for f in columns])

    # FileDAO
    columns = list(FileDAO._columns)
    columns.remove('id')
    FileDAO._sql_insert = "INSERT INTO file (%s) values (%s)" % (', '.join(columns), ', '.join([':%s' % f for f in columns]))
    FileDAO._sql_update = "UPDATE file SET %s WHERE id = :id" % ', '.join(['%s = :%s' % (f, f) for f in columns])

_init()

